package dao;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import view.DataList;

public class Score extends DBConnect {
    private final static String table = "score";
    public Score() {
    }

    public static int insert(long stud, String subject, BigDecimal decimal)
            throws SQLException {
        PreparedStatement stmt = getPrepareStatement(
                    "INSERT INTO `" + table + "` " + 
                    "(`student_id`, `subject`, `score`) VALUES (?, ?, ?)");
        stmt.setLong(1, stud);
        stmt.setString(2, subject);
        stmt.setBigDecimal(3, decimal);
        return stmt.executeUpdate();
    }

    public static PreparedStatement update(String where, String... feilds)
            throws SQLException {
        String sql = "UPDATE `" + table + "` " + "SET ";
        for (int i = 0; i < feilds.length; i++) {
            sql += "`" + feilds[i] + "`=?";
            if (i != feilds.length - 1)
                sql += ",";
        }
        sql += " WHERE " + where;
        return getPrepareStatement(sql);
    }

    public static DataList<Data> select() throws SQLException {
        ResultSet rs = createStatement()
            .executeQuery(
                    "SELECT " + Data.fields +
                    " FROM `" + table + "`");
        DataList<Data> data = new DataList<Data>();
        while (rs.next()) {
            data.add(Data.getData(rs));
        }
        return data;
    }

    public static DataList<Data> selectById(long id) throws SQLException {
        PreparedStatement stmt = getPrepareStatement(
                "SELECT " + Data.fields +
                " FROM `" + table + "`" +
                " WHERE `id`=?");
        stmt.setLong(1, id);
        ResultSet rs = stmt.executeQuery();
        DataList<Data> data = new DataList<Data>();
        while (rs.next()) {
            data.add(Data.getData(rs));
        }
        return data;
    }
    public static DataList<Data> selectByStuId(long id) throws SQLException {
        PreparedStatement stmt = getPrepareStatement(
                "SELECT " + Data.fields +
                " FROM `" + table + "`" +
                " WHERE `student_id`=?");
        stmt.setLong(1, id);
        ResultSet rs = stmt.executeQuery();
        DataList<Data> data = new DataList<Data>();
        while (rs.next()) {
            data.add(Data.getData(rs));
        }
        return data;
    }

    public static Data findById(long id) throws SQLException {
        PreparedStatement stmt =
            getPrepareStatement(
                    "SELECT " + Data.fields +
                    " FROM `" + table +
                    "` where id=?");
        stmt.setLong(1, id);
        ResultSet rs = stmt.executeQuery();
        return rs.next() ? Data.getData(rs) : null;
    }

    public static Data findByNoAndPwd(String no, String pwd)
            throws SQLException {
        PreparedStatement stmt =
            getPrepareStatement(
                    "SELECT " + Data.fields +
                    " FROM `" + table +
                    "` where no=? AND password=?");
        stmt.setString(1, no);
        stmt.setString(2, getMD5(pwd.getBytes()));
        ResultSet rs = stmt.executeQuery();
        return rs.next() ? Data.getData(rs) : null;
    }

    public static boolean deleteById(int id) throws SQLException {
        PreparedStatement stmt = getPrepareStatement(
                "DELETE FROM `" + table +
                "` WHERE `id`=?");
        stmt.setInt(1, id);
        return stmt.executeUpdate() > 0;
    }

    public static class Data {
        private long id;
        private long student_id;
        private String subject;
        private double score;

        public final static String fields = "id,student_id,subject,score";

        public static Data getData(ResultSet rs) throws SQLException {
            Data d = new Data();
            d.setId(rs.getInt(1));
            d.setStudent_id(rs.getInt(2));
            d.setSubject(rs.getString(3));
            d.setScore(rs.getDouble(4));
            return d;
        }
        
        public Data() {
            super();
        }

        public void setData(Data d) {
            this.id = d.id;
            this.student_id = d.student_id;
            this.subject = d.subject;
            this.score = d.score;
        }

        public long getId() {
            return id;
        }
        public void setId(long id) {
            this.id = id;
        }

        public long getStudent_id() {
            return student_id;
        }
        public void setStudent_id(long student_id) {
            this.student_id = student_id;
        }
        public String getSubject() {
            return subject;
        }
        public void setSubject(String subject) {
            this.subject = subject;
        }
        public double getScore() {
            return score;
        }
        public void setScore(double score) {
            this.score = score;
        }
    }
}
